package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.GroupType;
import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.Speak;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 发言dao类
 *
 * @author lyj
 * @since 2015-3-21
 */
@Repository
public class SpeakDao extends BaseDao {
    @Autowired
    private WebContext webContext;


    /**
     * 验证发言所在课程群组是否为登录用户所管理课程群组
     *
     * @param speakId 问题ID
     * @return int
     * @author lyj
     * @since 2015-3-25
     */
    public int checkGroup(final long speakId){
        //根据问题ID查询其所在课程群组ID，根据群组ID获取其负责教师工号，如果教师工号和登录教师ID对应
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_teacher WHERE id = :teacherId AND sNumber = (SELECT sTeacherNum FROM t_course_group WHERE id = (SELECT iGroupId FROM t_speak WHERE id = :id)) LIMIT 1");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", speakId)
                .addValue("teacherId", webContext.getUserId());

        log.debug("验证问题所在课程群组是否为登录用户所管理课程群组");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
    }


    /**
     * 验证发言所在的班级群组是否是老师所管理的班级群组
     *
     * @param speakId 发言ID
     * @param teacherNum 教师工号
     * @param groupType 群组类型
     * @return int
     * @author lyj
     * @since 2015-5-11
     */
    public int checkClassGroup(final long speakId, final String teacherNum, final int groupType) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_speak WHERE tiGroupType = :groupType AND iGroupId IN (SELECT id FROM t_class WHERE sTeacherNum = :teacherNum) AND id = :speakId");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("groupType", groupType)
                .addValue("teacherNum", teacherNum)
                .addValue("speakId", speakId);

        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
    }


    /**
     * 验证发言所在的专业群组是否是老师所管理的专业群组
     *
     * @param speakId 发言ID
     * @param groupType 群组类型
     * @return int
     * @author lyj
     * @since 2015-5-11
     */
    public int checkSpecGroup(final long speakId, final int groupType) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_speak WHERE tiGroupType = :groupType AND iGroupId IN (SELECT id FROM t_specialty WHERE iLeaderId = :teacherId) AND id = :speakId");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("groupType", groupType)
                .addValue("teacherId", webContext.getUserId())
                .addValue("speakId", speakId);

        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);

    }

    //<editor-fold desc="新增">
    /**
     * 新增发言
     *
     * @param speak 发言实体
     * @return int
     * @author lyj
     * @since 2015-3-21
     */
    public int addSpeak(Speak speak){

        StringBuilder sql = new StringBuilder("INSERT INTO t_speak VALUES (NULL, :sContent, :iCreatorId, :tiRole, NOW(), :iGroupId, :tiGroupType)");

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("sContent", speak.getContent());

        param.addValue("iCreatorId", webContext.getUserId());
        param.addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);
        param.addValue("iGroupId", speak.getGroupId());
        param.addValue("tiGroupType", speak.getGroupType());

        log.debug("新增发言");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }
    //</editor-fold>


    /**
     * 删除发言
     *
     * @param id 发言ID
     * @return int
     * @author lyj
     * @since 2015-3-21
     */
    public int deleteSpeak(final long id){
        StringBuilder sql = new StringBuilder("DELETE FROM t_speak WHERE id = :id");

        log.debug("删除发言");
        return namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("id", id));
    }


    /**
     * 判断创建人
     *
     * @param id 发言ID
     * @return int
     * @author lyj
     * @since 2015-3-21
     */
    public int checkCreator(final long id){
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_speak WHERE id = :id AND iCreatorId = :iCreatorId  AND tiRole = :tiRole");


        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", id)
                .addValue("iCreatorId", webContext.getUserId())
                .addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);


        log.debug("判断创建人");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
    }



    /**
     * 获取发言列表
     *
     * @param groupType 群组类型（0非群组 1课程群组 2专业群组 3班级群组）
     * @param groupId 群组ID
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @param creatorId 创建人ID

     * @return page
     * @author lyj
     * @since 2015-3-21
     */
    public Page<Speak> getList(final int groupType, final long groupId, final int pageNum, final int pageSize, final long creatorId){
        StringBuilder sql = new StringBuilder("SELECT id, sContent, iCreatorId, tiRole, dtCreate FROM t_speak ");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(*) FROM t_speak");

        StringBuilder where = new StringBuilder(" WHERE 1=1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤群组类型

        if (groupType >= GroupType.NOT_GROUP && groupType <= GroupType.CLASS_GROUP){
            log.debug("过滤群组类型groupType >= GroupType.NOT_GROUP && groupType <= GroupType.CLASS_GROUP");
            where.append(" AND tiGroupType = :tiGroupType");
            param.addValue("tiGroupType", groupType);
        }

        //过滤群组ID
        if (groupId > 0){
            log.debug("过滤群组IDgroupId > 0");
            where.append(" AND iGroupId = :iGroupId");
            param.addValue("iGroupId", groupId);
        }

        //过滤创建人
        if (creatorId > 0 ){
            log.debug("过滤创建人creatorId > 0");
            where.append(" AND iCreatorId = :iCreatorId AND tiRole = :tiRole");
            param.addValue("iCreatorId", creatorId);
            param.addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {
            log.debug("获取总条数total == 0");
            return new Page<>();
        }

        //追加排序
        where.append(" ORDER BY dtCreate DESC");

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Speak> speakList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Speak>() {
            @Override
            public Speak mapRow(ResultSet rs, int rowNum) throws SQLException {
                Speak speak = new Speak();

                speak.setId(rs.getLong("id"));
                speak.setContent(rs.getString("sContent"));
                speak.setCreatorId(rs.getLong("iCreatorId"));
                speak.setRole(rs.getInt("tiRole"));
                speak.setCreate(rs.getTimestamp("dtCreate"));


                return speak;
            }
        });

        log.debug("获取发言列表");
        return new Page<>(total, pageSize, pageNum, speakList);
    }


    /**
     * 根据ID获取发言信息
     * @param speakId
     * @return speak
     * @author lyj
     * @since 2015-4-23
     */
    public Speak getById(final long speakId) {
        StringBuilder sql = new StringBuilder("SELECT * FROM t_speak WHERE id = :id");

        try {
            log.debug("根据ID获取发言信息");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", speakId), new RowMapper<Speak>() {
                @Override
                public Speak mapRow(ResultSet resultSet, int i) throws SQLException {
                    Speak speak = new Speak();

                    speak.setId(resultSet.getLong("id"));
                    speak.setContent(resultSet.getString("sContent"));
                    speak.setCreatorId(resultSet.getLong("iCreatorId"));
                    speak.setRole(resultSet.getInt("tiRole"));
                    speak.setCreate(resultSet.getTimestamp("dtCreate"));
                    speak.setGroupId(resultSet.getLong("iGroupId"));
                    speak.setGroupType(resultSet.getInt("tiGroupType"));

                    return speak;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("EmptyResultDataAccessException,根据id获取发言信息为空id->{}");
            log.warn("根据id获取发言信息为空id->{}", speakId);
            return null;
        }

    }

}
